package cn.com.zsc.hr;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.Hashtable;
import java.util.Vector;

import javax.servlet.ServletException;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import org.apache.commons.lang.StringUtils;

import cn.com.zsc.hr.vo.MonthAttendanceVO;
import cn.com.zsc.util.ConstValue;
import cn.com.zsc.util.DBConnect;
import cn.com.zsc.util.MathExtend;

import com.jarp.db.DAO;

/**
 * 考勤处理类
 * 
 * @author jamison
 * 
 */
public class AttendanceManager {
	
	/**
	 * 从考勤系统中拷贝考勤信息到应用管理系统
	 */
	@SuppressWarnings("unchecked")
	public void copyAttInfoToSys() {
		// 获取数据库链接
		Connection conn = DBConnect.getConnection();
		Statement statement = null;
		ResultSet rs = null;
		
		// 获取当天日期
		SimpleDateFormat bartDateFormat = new SimpleDateFormat("yyyy-MM-dd");
		String date = bartDateFormat.format(new Date());

		DAO dao = new DAO();

		try {
			// 删除应用程序对应数据库中当天的考勤记录
			String delCurDateSql = "delete from hr_atdrecord where recdate='" + date + "'";
			dao.execCommand(ConstValue.DBNAME, delCurDateSql);

			// 查询数据字典中的考勤时间
			String queryAttTimeSql = "select cl_code,cl_name,parm from cm_commlist where cl_code = '228'";
			Vector<Hashtable<String, String>> attTime  = dao.getDataSet(ConstValue.DBNAME, queryAttTimeSql);

			SimpleDateFormat formatStatus = new SimpleDateFormat("h:mm");
			
			Date rootStatus = null;
			Date gridStatus = null;

			if (attTime != null && attTime.size() > 0) {
				rootStatus = formatStatus.parse(attTime.get(0).get("PARM").toString());
			}

			// 查询指定日期的考勤记录
			String queryAttInfoByDate = "select A.WorkID, B.RecDate, min(B.RecTime) AS RecTime from hremployee A " +
								"left join atdrecord B on A.EmplID=B.EmplID where B.RecDate='" + date + "' group by A.WorkID, B.RecDate";
			statement = conn.createStatement();
			rs = statement.executeQuery(queryAttInfoByDate);
			
			// 拷贝考勤记录sql集合
			ArrayList insertSQLList = new ArrayList();
			String insertSQL = "";
			String status = "";
			
			if (rs != null) {
				while (rs.next()) {
					// 打卡时间
					String recTime = rs.getString("RecTime");
					String workID = rs.getString("workID");
					
					if (StringUtils.isEmpty(recTime)) {
						// 表示职工休息
						status = "229";
					} else {
						gridStatus = formatStatus.parse(recTime);

						if (gridStatus.before(rootStatus)) {
							status = "230";// 表示职工考勤状态为正常的
						} else {
							status = "231";// 表示职工考勤状态为迟到的
						}
					}
					
					insertSQL = "insert into hr_atdrecord (workID,recDate,RecTime,status) values ('"+ workID+ "','"+ date+ "','"+ recTime+ "','"+ status+ "')";
					insertSQLList.add(insertSQL);
				}
			}

			if (insertSQLList.size() > 0) {
				dao.execCommand(ConstValue.DBNAME, insertSQLList);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ServletException e) {
			e.printStackTrace();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (rs != null) {
					rs.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				try {
					if (statement != null) {
						statement.close();
					}
				} catch (SQLException e) {
					e.printStackTrace();
				} finally {
					try {
						if (conn != null) {
							conn.close();
						}
					} catch (SQLException e) {
						e.printStackTrace();
					}
				}
			}
		}
	}
	
	/**
	 * 在本系统查询日考勤信息
	 * @param json
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public String getDayInfo(String json) {
		JSONObject jsobject = JSONObject.fromObject(json);
		
		String recdate = jsobject.getString("recDate");
		String workID = jsobject.getString("workID");
		String emplName = jsobject.getString("emplName");

		String execSQL = "select A.WORKID, " +
								"A.ENAME as SHOW_ENAME," +
								"B.RECDATE," +
								"B.RECTIME," +
								"C.CL_NAME as SHOW_DUTIES," +
								"D.CL_NAME as SHOW_TERM," +
								"E.CL_NAME as SHOW_STATUS " +
						 " from hr_employee A "
								+ " LEFT JOIN hr_atdrecord B  on A.workID = B.workID "
								+ " LEFT JOIN CM_COMMLIST C  on A.duties = C.CL_CODE "
								+ " LEFT JOIN CM_COMMLIST D  on  A.term = D.CL_CODE "
								+ " LEFT JOIN CM_COMMLIST E  on B.status = E.CL_CODE "
								+ " where 1=1 ";

		if (recdate != null && !recdate.trim().equals("")) {
			String tempdate = String.format("yyyy-MM-dd", recdate);
			tempdate = recdate.substring(0, 10);
			execSQL = execSQL + " and B.RECDATE='" + tempdate + "'";
		}
		
		if (workID != null && !workID.trim().equals("")) {
			execSQL = execSQL + " and A.WORKID  like '%" + workID + "%'";
		}
		
		if (emplName != null && !emplName.trim().equals("")) {
			execSQL = execSQL + " and A.ENAME like '%" + emplName + "%'";
		}
		
		execSQL += " order by A.WORKID desc";
		
		String resultstr = ConstValue.EMPTY_RECORD_NOT_PAGEING;
		
		try {
			DAO dao = new DAO();
			
			Vector<Hashtable<String, String>> v = dao.getDataSet(ConstValue.DBNAME, execSQL);

			if (v!=null && v.size()>0) {
				JSONArray jsonarr = JSONArray.fromObject(v);
				
				resultstr = jsonarr.toString();
			} 
		} catch (Exception e) {
			e.printStackTrace();
			return "数据库操作失败";
		}
		
		return resultstr;
	}

	/**
	 * 在本系统查询日考勤信息
	 * @param json
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public String getDayInfo() {
		DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
		String today = df.format(new Date());
		
		DAO dao = new DAO();

		String execSQL = "select A.WORKID, " +
								"A.ENAME as SHOW_ENAME," +
								"B.RECDATE," +
								"B.RECTIME," +
								"C.CL_NAME as SHOW_DUTIES," +
								"D.CL_NAME as SHOW_TERM," +
								"E.CL_NAME as SHOW_STATUS " +
						 " from hr_employee A "
								+ " LEFT JOIN hr_atdrecord B  on A.workID = B.workID "
								+ " LEFT JOIN CM_COMMLIST C  on A.duties = C.CL_CODE "
								+ " LEFT JOIN CM_COMMLIST D  on  A.term = D.CL_CODE "
								+ " LEFT JOIN CM_COMMLIST E  on B.status = E.CL_CODE "
								+ " where B.RECDATE='" + today + "' order by A.workID desc";
		
		String resultstr = ConstValue.EMPTY_RECORD_NOT_PAGEING;
		
		try {
			Vector<Hashtable<String, String>> vector = dao.getDataSet(ConstValue.DBNAME, execSQL);
			
			JSONArray json = JSONArray.fromObject(vector);
			
			resultstr = json.toString();
		} catch (Exception e) {
			e.printStackTrace();
			return "数据库操作失败";
		}
		
		return resultstr;
	}
	
	/**
	 * 在本系统查询月考勤信息
	 * @param month
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public String getMonthAttendance(String month) {
		DAO dao = new DAO();
		
		// 考勤日期
		String day = "";
		
		// 考勤日期中的XX号
		String tempDay = "";
		
		// 考勤状态
		int status = 0;
		
		// 出勤天数
		int attendance = 0;
		
		// 公休
		int holidays = 0;
		
		// 每月最大天数
		int maxDayOfMonth = 0;
		
		// 出勤率
		String attendanceRate = null;
		
		// 状态图标
		String stateIcon = null;
		
		String resultStr = ConstValue.EMPTY_RECORD_NOT_PAGEING;
		
		ArrayList<MonthAttendanceVO> monthAttList = new ArrayList<MonthAttendanceVO>();
		
		try {
			// 取出所有的考勤员工
			String getEmpSql = "select WORKID from hr_atdrecord group by WORKID";
			Vector<Hashtable<String, String>> workIDVector = dao.getDataSet(ConstValue.DBNAME, getEmpSql);

			String getMonthAttendanceByWorkID = "";
			
			if (workIDVector != null && workIDVector.size() > 0) {
				for (int i=0; i<workIDVector.size(); i++) {
					String workID = workIDVector.get(i).get("WORKID");
					
					getMonthAttendanceByWorkID = "select " +
														"A.*, " +
												 		"B.ENAME, " +
												 		"C.PARM " +
												 "from hr_atdrecord A " +
												 		"left join hr_employee B on A.WORKID=B.WORKID " +
											 			"left join cm_commlist C on A.STATUS=C.CL_CODE " +
												 "where left(A.RECDATE,7)='" + month + "' and A.WORKID='" + workID + "' order by RECDATE";
					
					// 遍历员工月考勤
					Vector<Hashtable<String, String>> empMonthAttendance = dao.getDataSet(ConstValue.DBNAME, getMonthAttendanceByWorkID);
					
					if (empMonthAttendance != null && empMonthAttendance.size() > 0) {
						MonthAttendanceVO monthAttendance = new MonthAttendanceVO();
						
						for (int j=0; j<empMonthAttendance.size(); j++) {
							day = empMonthAttendance.get(j).get("RECDATE");
							tempDay = day.substring(8, day.length());
							
							stateIcon = empMonthAttendance.get(j).get("PARM");
							
							monthAttendance.setGuideName(empMonthAttendance.get(j).get("ENAME"));
							
							if ("01".equals(tempDay)) {
								monthAttendance.setDay_1(stateIcon);
							} else if ("02".equals(tempDay)) {
								monthAttendance.setDay_2(stateIcon);
							} else if ("03".equals(tempDay)) {
								monthAttendance.setDay_3(stateIcon);
							} else if ("04".equals(tempDay)) {
								monthAttendance.setDay_4(stateIcon);
							} else if ("05".equals(tempDay)) {
								monthAttendance.setDay_5(stateIcon);
							} else if ("06".equals(tempDay)) {
								monthAttendance.setDay_6(stateIcon);
							} else if ("07".equals(tempDay)) {
								monthAttendance.setDay_7(stateIcon);
							} else if ("08".equals(tempDay)) {
								monthAttendance.setDay_8(stateIcon);
							} else if ("09".equals(tempDay)) {
								monthAttendance.setDay_9(stateIcon);
							} else if ("10".equals(tempDay)) {
								monthAttendance.setDay_10(stateIcon);
							} else if ("11".equals(tempDay)) {
								monthAttendance.setDay_11(stateIcon);
							} else if ("12".equals(tempDay)) {
								monthAttendance.setDay_12(stateIcon);
							} else if ("13".equals(tempDay)) {
								monthAttendance.setDay_13(stateIcon);
							} else if ("14".equals(tempDay)) {
								monthAttendance.setDay_14(stateIcon);
							} else if ("15".equals(tempDay)) {
								monthAttendance.setDay_15(stateIcon);
							} else if ("16".equals(tempDay)) {
								monthAttendance.setDay_16(stateIcon);
							} else if ("17".equals(tempDay)) {
								monthAttendance.setDay_17(stateIcon);
							} else if ("18".equals(tempDay)) {
								monthAttendance.setDay_18(stateIcon);
							} else if ("19".equals(tempDay)) {
								monthAttendance.setDay_19(stateIcon);
							} else if ("20".equals(tempDay)) {
								monthAttendance.setDay_20(stateIcon);
							} else if ("21".equals(tempDay)) {
								monthAttendance.setDay_21(stateIcon);
							} else if ("22".equals(tempDay)) {
								monthAttendance.setDay_22(stateIcon);
							} else if ("23".equals(tempDay)) {
								monthAttendance.setDay_23(stateIcon);
							} else if ("24".equals(tempDay)) {
								monthAttendance.setDay_24(stateIcon);
							} else if ("25".equals(tempDay)) {
								monthAttendance.setDay_25(stateIcon);
							} else if ("26".equals(tempDay)) {
								monthAttendance.setDay_26(stateIcon);
							} else if ("27".equals(tempDay)) {
								monthAttendance.setDay_27(stateIcon);
							} else if ("28".equals(tempDay)) {
								monthAttendance.setDay_28(stateIcon);
							} else if ("29".equals(tempDay)) {
								monthAttendance.setDay_29(stateIcon);
							} else if ("30".equals(tempDay)) {
								monthAttendance.setDay_30(stateIcon);
							} else if ("31".equals(tempDay)) {
								monthAttendance.setDay_31(stateIcon);
							}
							
							status = Integer.parseInt(empMonthAttendance.get(j).get("STATUS"));
							
							if (status == 230) {
								attendance++;
							} else if (status == 229) {
								holidays++;
							}
							
							// 计算当月最大天数
							maxDayOfMonth = getMaxDayOfMonth(day);
							monthAttendance.setMonthDays(maxDayOfMonth);
							
							// 计算出勤率
							attendanceRate = new DecimalFormat("#.00").format(MathExtend.divide(attendance, maxDayOfMonth - holidays, 4)*100) + "%";
						}
						
						monthAttendance.setAttendance(attendance);
						monthAttendance.setHolidays(holidays);
						monthAttendance.setAttendanceRate(attendanceRate);
						monthAttList.add(monthAttendance);
						
						attendance = 0;
						holidays = 0;
					}
				}
			}
			
			JSONArray json = JSONArray.fromObject(monthAttList);
			
			resultStr = json.toString();
		} catch (Exception e) {
			e.printStackTrace();
			return "数据库操作失败";
		}
		
		return resultStr;
	}
	
	/**
	 * 修改考勤状态
	 * @param jso
	 * @return
	 */
	public String updateAttendance(String jso) {
		JSONArray array = JSONArray.fromObject(jso);

		DAO dao = new DAO();
		ArrayList<String> sqlList = new ArrayList<String>();
		
		String sql = "";
		
		for (int i=0; i<array.length(); i++) {
			JSONObject jsobject = array.getJSONObject(i);
			
			String status = jsobject.getString("status");
			String workID = jsobject.getString("workID");
			String recDate = jsobject.getString("recDate");
			String recTime = jsobject.getString("recTime");
			
			sql = "update hr_atdrecord set STATUS='" + status + "', RECTIME='" + recTime + "' where WORKID='" + workID + "' and RECDATE='" + recDate + "'";
		
			sqlList.add(sql);
		}
		
		try {
			boolean flag = dao.execCommand(ConstValue.DBNAME, sqlList);
			
			if (flag) {
				return "success";
			} else {
				return "数据库操作失败";
			}
		} catch (Exception e) {
			e.printStackTrace();
			return "数据库操作失败";
		}
	}
	
	/**
	 * 获取指定月份的最大天数
	 * @param date
	 * @return
	 */
	public int getMaxDayOfMonth(String date) {
		DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
		Calendar calendar = Calendar.getInstance();
		
		try {
			calendar.setTime(df.parse(date));
		} catch (ParseException e) {
			e.printStackTrace();
		}
		
		return calendar.getActualMaximum(Calendar.DATE);
	}
}
